package com.glq.book.dao;

import com.glq.book.entity.Book;
import com.glq.book.util.JdbcUtil;
import com.glq.book.vo.BookVO;
import jdk.nashorn.internal.scripts.JD;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    private Connection connection = null;
    private ResultSet resultSet = null;
    private PreparedStatement preparedStatement = null;
    private String sql = null;

    /**
     * 采购书（管理员）l
     *
     * @param book
     * @return
     */
    public boolean insertBook(BookVO book) {
        try {
            sql = "insert into " +
                    "book(book_name,book_price,book_number,book_type,book_fengmian,book_author,book_abstract) " +
                    "values(?,?,?,?,?,?,?)";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, book.getBookName());
            preparedStatement.setInt(2, book.getBookPrice());
            preparedStatement.setInt(3, book.getBookNumber());
            preparedStatement.setString(4, book.getBookType());
            preparedStatement.setString(5, book.getBookFengMian());
            preparedStatement.setString(6, book.getBookAuthor());
            preparedStatement.setString(7, book.getbookAbstract());
            return preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return true;
    }

    /**
     * 下架书（管理员）
     *
     * @param id
     * @return
     */
    public boolean deleteBook(Integer id) {
        try {
            sql = "delete from book where id = ?";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            return preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return true;
    }

    /**
     * 修改书的价格（管理员）
     *
     * @param bookPrice
     * @param bookFengMian
     * @return
     */
    public boolean updateBook(Integer bookPrice, Integer id, String bookFengMian, String bookName, String bookType, String bookAuthor, String bookAbstract, Integer bookNumber) {
        try {
            connection = JdbcUtil.getConnection();
            sql = "update book " +
                    "set book_price = ?," +
                    "book_fengmian = ?," +
                    "book_name = ?," +
                    "book_type=?," +
                    "book_author =?," +
                    "book_abstract = ? ," +
                    "book_number = ? " +
                    "where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, bookPrice);
            preparedStatement.setString(2, bookFengMian);
            preparedStatement.setString(3, bookName);
            preparedStatement.setString(4, bookType);
            preparedStatement.setString(5, bookAuthor);
            preparedStatement.setString(6, bookAbstract);
            preparedStatement.setInt(7, bookNumber);
            preparedStatement.setInt(8, id);

            return preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return true;
    }

    /**
     * 借书和买书（用户），书籍数量减少
     *
     * @param bookId
     * @return
     */
    public boolean subBookNumber(Integer bookId, Integer number) {
        try {
            connection = JdbcUtil.getConnection();
            sql = "update book set book_number = book_number-? where book_number - ?>0 and id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, number);
            preparedStatement.setInt(2, number);
            preparedStatement.setInt(3, bookId);
            return preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return true;
    }

    /**
     * 还书（用户），数据数量增加
     *
     * @param bookName
     * @return
     */
    public boolean addBookNumber(String bookName, Integer number) {
        try {
            connection = JdbcUtil.getConnection();
            sql = "update book set book_number = book_number + ? where book_number > 0 and book_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, number);
            preparedStatement.setString(2, bookName);
            return preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return true;
    }

    /**
     * 查询所有图书
     */
    public List<Book> selectBookList() {
        List<Book> list = new ArrayList<>();
        try {
            sql = "select * from book order by book_name DESC ";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new Book(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 查询具体某一本图书
     */
    public Book selectBookById(Integer id) {
        Book book = null;
        try {
            sql = "select * from book where id = ?";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                book = new Book(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return book;
    }


    public List<String> selectType() {
        List<String> list = new ArrayList<>();
        try {
            sql = "select book_type from book group by book_type DESC ";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return list;
    }

    public List<Book> selectBookListByType(String typeName) {
        List<Book> list = new ArrayList<>();
        try {
            sql = "select * from book where book_type=? order by book_name DESC ";
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, typeName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new Book(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return list;
    }
}
